from flask import Blueprint,request,make_response,jsonify
import os
from utils.myfile import myf
from xlrd import open_workbook
from db import db1

from io import BytesIO
from xlrd import open_workbook
from urllib.parse import quote
import xlsxwriter
import datetime
import mimetypes
from config import get_data

bp_execl=Blueprint("bp_execl",__name__)

@bp_execl.route('/upload',methods=['POST'])
def upload():
    # 获取文件
    file=request.files['file']
    newname=myf.rename(file.filename)
    # 上传文件
    file.save(os.path.join('./static/files/'+newname))
    # 打开文件读取
    name = 'static/files/'+newname
    # newname='643ff81c57384de89e485d18fd20b9b3.xls'
    # name='static/files/'+newname
    bk = open_workbook(name, encoding_override='utf-8')
    sh = bk.sheets()[0]
    nrows = sh.nrows
    ncols = sh.ncols
    for i in range(2, nrows):
        row_date = sh.row_values(i)
        for j in range(0,ncols):
            print(row_date[j])


    return 'ok'


@bp_execl.route('/download', methods=["GET", "POST"])
def download():
    # 实例化二进制数据
    out = BytesIO()
    # 创建一个Excel实例
    workbook = xlsxwriter.Workbook(out)
    # 添加一个sheet，add_worksheet()，默认"Sheet1"，可自定义命名，add_worksheet("资产列表")
    table = workbook.add_worksheet()
    # table.write(行, 列, 字段名)
    # 行、列从 0 开始计数
    # 字段命名根据每个人使用情况而定，插入数据部分也是
    sql="select * from test limit 0,10"
    res=db1.find_all(sql)
    table.write(0, 0, "资产编号")
    table.write(0, 1, "资产名称")
    num=1
    for i in res:
        table.write(num, 0, i['id'])
        table.write(num, 1, i['name'])
        num+=1

    workbook.close()
    filename = quote("资产列表_{}.xls".format(datetime.datetime.now().strftime("%Y%m%d%H%M%S")))
    file = make_response(out.getvalue())
    out.close()
    mime_type = mimetypes.guess_type('%s.xls' % filename)[0]
    file.headers['Content-Type'] = mime_type
    file.headers["Cache-Control"] = "no-cache"
    file.headers['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(filename)
    return file

def xtree(data):
    if not data:
        return data
    tree={}
    list=[]
    for i in data:
        tree[i['id']]=i
    for j in data:
        pid=j['pid']
        if pid==0:
            list.append(j)
        else:
            if 'son'not in tree[pid]:
                tree[pid]['son']=[]
            tree[pid]['son'].append(j)
    return list




@bp_execl.route('/treecom')
def treecom():
    sql="select * from treecompany"
    res=db1.find_all(sql)
    print(res)
    data=xtree(res)
    print(data)
    return jsonify({'code':200,'list':data})


@bp_execl.route('/addcompany',methods=['POST'])
def addcompany():
    data=get_data()
    sql="insert into treecompany(name,pid,code) values('%s',%d,'%s')"%(str(data['name']),int(data['pid']),str(data['code']))
    db1.update(sql)
    return jsonify({'code':200})

# from utils.es import Es
# @bp_execl.route('/deptesinsert')
# def deptesinsert():
#     es=Es('testes','test-type')
#     # 插入数据
#     sql='select * from test limit 0,10'
#     res=db1.find_all(sql)
#     for i in res:
#         es.insert(i)
#     return 'ok'

















